<?php

class mmerchan extends CI_Model {
    
    function mmerchan() {
		parent::__construct();
		$this->load->library('calendar');
		$this->load->model('mmaster');
		$this->load->library('serversidedatatables');
	}
    
    public function getPaymentOption() {
        //get all records from users table
        $query = $this->db->get('v_combo_payment_method');
        
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll
    
    
    public function getById( $id ) {
        $id = intval( $id );
        
        $query = $this->db->where( 'so_id', $id )->limit( 1 )->get( 't_trx_sales_order' );
        
        if( $query->num_rows() > 0 ) {
            return $query->row();
        } else {
            return array();
        }
    }
    
    public function getRegion()
    {
        $sql = "select territory_id as mvalue, upper(territory_name) as mdisplay
                from t_mtr_territory
                where parent_id = 1 and territory_type_id = 2
                ORDER BY territory_name asc;";  
                 
        $query = $this->db->query($sql);
        /**
  * $this->db->select('territory_id, territory_name, parent_id');
  * 		$this->db->from('t_mtr_territory');
  * 		$this->db->where('territory_id !=', 1);
  *         $this->db->where('parent_id', 1);
  *         $this->db->order_by("territory_name", "asc");
  */ 
		//$query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
        //return $query->result();
    }
    
    public function getCluster($region_id)
    {
        //$this->db->select('territory_id, territory_name, parent_id');
//		$this->db->from('t_mtr_territory');
//        $this->db->where('parent_id', $region_id);
//        $this->db->order_by("territory_name", "asc");
//		$query = $this->db->get();

        $sql = "select territory_id as mvalue, CONCAT_WS(' ',split_part(upper(territory_name), ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)) AS mdisplay
                from t_mtr_territory
                where parent_id = $region_id and territory_type_id = 3
                order by split_part(territory_name, ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)";  
                 
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }

        return $query->result_array();
        
    }
    
    public function getMerchan($cluster_id = null, $limit = NULL, $offset = NULL)
    {
        //tgl
        //created_ons
        //partner_name
        //keyword
        //pic_type
        //image
		/**anggwa@gmail.com 20120507 batasin query jika admin bebas, jika cc hanya menampilkan clusternya saja */
     	$userlogin = $this->session->userdata('username');

		if ($userlogin == "admin") {
        $sql = "SELECT CAST(cp.created_on AS DATE)  as tgl,
                 to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
                 f_get_channel_name(channel_id) as partner_name,
                 keyword, lk.lookup_desc as pic_type, cp.image
                 FROM t_trx_merchan_capture cp, t_mtr_lookup lk, t_mtr_territory tr
                WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
                AND lk.lookup_key = 'pic_type'
                AND tr.territory_id = f_get_territory(channel_id)
                AND tr.parent_id = $cluster_id
                and tgl between date(now())-7 and date(now)
                ORDER BY tgl DESC, partner_name ASC, pic_type ASC
                LIMIT $limit OFFSET $offset";
		
		} else
		{
        $sql = "SELECT CAST(cp.created_on AS DATE)  as tgl,
                 to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
                 f_get_channel_name(channel_id) as partner_name,
                 keyword, lk.lookup_desc as pic_type, cp.image
                 FROM t_trx_merchan_capture cp, t_mtr_lookup lk, t_mtr_territory tr
                WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
                AND lk.lookup_key = 'pic_type'
                AND tr.territory_id = f_get_territory(channel_id)
                and tgl between date(now())-3 and date(now)
                ORDER BY tgl DESC, partner_name ASC, pic_type ASC
                LIMIT $limit OFFSET $offset";		
		};
//                                AND tr.parent_id = f_get_cluster('$userlogin')

                
        $merchan = $this->db->query($sql);
        if( $merchan->num_rows() > 0 ) {
            return $merchan->result_array();
        } else {
            return 'false';
        }
        
        /** ################################################ cadangan ################################*/
        /*
        $this->db->select('*');
		$this->db->from('t_mtr_outlet');
        $this->db->join('t_trx_merchan_capture', 't_trx_merchan_capture.channel_id = t_mtr_outlet.outlet_id');
        $this->db->join('t_keywords', 't_trx_merchan_capture.user_id = t_keywords.user_id and cast(t_keywords.valid_date as date) = cast(t_trx_merchan_capture.created_on as date) and t_trx_merchan_capture.istatus = 1');
        //$this->db->where('territory_id', $cluster_id);
        //$this->db->where('parent_id', $cluster_id);
        //where territory_id in (select ter.territory_id from t_mtr_territory ter, t_mtr_outlet otl where ter.territory_id = otl.territory_id and ter.parent_id = 36)

		$query = $this->db->get();

        return $query->result_array();
        */
        
    }
    
    public function getUserGroupId($userid)
    {
        $this->db->select('user_group_id');
		$this->db->from('t_mtr_user');
		$this->db->where('user_id', $userid);
        $this->db->limit(1, 0);
		$query = $this->db->get(); 
        return $query->row_array();    
    }
    
    public function totalMerchan($clusterid = null)
    {
        $userlogin = $this->session->userdata('username');

		if ($userlogin == "admin") {
        $sql = "SELECT CAST(cp.created_on AS DATE)  as tgl,
                 to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
                 f_get_channel_name(channel_id) as partner_name,
                 keyword, lk.lookup_desc as pic_type, cp.image
                 FROM t_trx_merchan_capture cp, t_mtr_lookup lk, t_mtr_territory tr
                WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
                AND lk.lookup_key = 'pic_type'
                AND tr.territory_id = f_get_territory(channel_id)
                AND tr.parent_id = $clusterid
                ORDER BY tgl DESC, partner_name ASC, pic_type ASC";
		
		} else
		{
        $sql = "SELECT CAST(cp.created_on AS DATE)  as tgl,
                 to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
                 f_get_channel_name(channel_id) as partner_name,
                 keyword, lk.lookup_desc as pic_type, cp.image
                 FROM t_trx_merchan_capture cp, t_mtr_lookup lk, t_mtr_territory tr
                WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
                AND lk.lookup_key = 'pic_type'
                AND tr.territory_id = f_get_territory(channel_id)
                ORDER BY tgl DESC, partner_name ASC, pic_type ASC";
		
		};
                
        $merchan = $this->db->query($sql);
        $count = $merchan->num_rows();
        return $count;     
    }
    
    public function getPaymentMethod()
    {
        $this->db->select('*')->from('v_combo_payment_method');
        $query = $this->db->get();   
 
        return $query->result();   
    }
    
    public function get_iccid()
    {
        $this->db->select('t_trx_scan_in_detail.iccid,
                            t_mtr_item_group.item_group_name,
                            t_mtr_item.item_name,
                            t_mtr_item.reseller_price,
                            t_mtr_item.default_price');
		$this->db->from('t_trx_scan_in_detail');
		$this->db->join('t_mtr_item', 't_trx_scan_in_detail.iccid = t_mtr_item.iccid AND t_mtr_item.istatus = 1');
        $this->db->join('t_mtr_item_group', 't_mtr_item.item_group_id = t_mtr_item_group.item_group_id');
		$query = $this->db->get();
        return $query->result_array();
    }
    
    public function get_single_iccid($iccid)
    {
        $this->db->select('*');
		$this->db->from('t_mtr_item');
        $this->db->join('t_mtr_item_group', 't_mtr_item.item_group_id = t_mtr_item_group.item_group_id');
        $this->db->where("iccid = '$iccid'");
		$query = $this->db->get();
        return $query->row_array();
    }
    
    function saveSalesOrder()
    {
        $dblokal = $this->load->database("default", true);
        $territory_id = $this->input->post("territory_id");
        $so_date = $this->input->post("so_date");
        $sales_id = $this->input->post("sales_id");
        $discount_total = $this->input->post("discount_total");
        $cash_paid = $this->input->post("cash_paid");
        $payment_method = $this->input->post("dListPaymentMethod");

        $data = array("territory_id" => $territory_id, "so_date" => $so_date, "sales_id" => $sales_id,
            "discount" => $discount_total, "cash_paid" => $cash_paid, "payment_method" => $payment_method, "created_by" => $this->session->userdata('username'), "updated_by" => $this->session->userdata('username'));

        
        $dblokal->insert("t_trx_sales_order", $data);
        $freshSalesOrder = $this->getFreshSalesOrder();
        return $freshSalesOrder;
        
    }
    
    function saveSalesOrderDetail($so_id, $i)
    {
        $dblokal = $this->load->database("default", true);
        $iccid = $this->input->post('iccid');
        $item_code = $this->input->post('item_code');
        $item_group_name = $this->input->post('item_group_name');
        $item_name = $this->input->post('item_name');
        $price = $this->input->post('default_price');
        $discount = $this->input->post('reseller_disc');
       
        
        $data = array("iccid" => $iccid[$i], "so_id" => $so_id, "price" => $price[$i], "discount" => $discount[$i], "created_by" => $this->session->userdata('username'), "updated_by" => $this->session->userdata('username'));

        
        $dblokal->insert("t_trx_sales_order_detail", $data);
        
    }
    
    function getFreshSalesOrder()
    {
        $this->db->select('*')->from('t_trx_sales_order');
        $this->db->order_by('so_id', 'desc');
        $query = $this->db->get();   
 
        return $query->row();    
        
    }
    
    function lookup($keyword){
        $this->db->select('*')->from('t_mtr_user');
        $this->db->like('user_name',$keyword,'after');
        $query = $this->db->get();   
 
        return $query->result();
    }
    
    public function getAll() {
        //get all records from users table
        $query = $this->db->get( 't_trx_sales_order' );
        
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll
    
    function lookup_iccid($keyword)
    {
        $this->db->select('*')->from('t_mtr_item');
        $this->db->like('iccid',$keyword,'after');
        
        $query = $this->db->get();   
 
        return $query->result_array();    
    }
    
    function getMerchantssdfdf($image_type='')
    {
            $sIndexColumn = "created_on"; //index column untuk kecepatan akses data
			$sTable = "t_trx_merchan_capture"; //table pertama
			$defaultOrder = "1"; //default order berdasarkan field yg ada pada $aColumns
            // $orderingSequence = "desc";
			$userlogin = $this->session->userdata('username'); //user credentials
            $cluster_id = $this->mglobal->user_cluster($userlogin);
            echo $cluster_id; die();
			$aColumns = array("created_on", "tanggal", "partner_name", "keyword", "sales_name", "image_url"); //field2 yg akan ditampilkan pada dataTables
            $flag = "";
			
            //$sql = "SELECT CAST(cp.created_on AS DATE)  as tgl,
//                 to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
//                 f_get_channel_name(channel_id) as partner_name,
//                 keyword, lk.lookup_desc as pic_type, cp.image
//                 FROM t_trx_merchan_capture cp, t_mtr_lookup lk, t_mtr_territory tr
//                WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
//                AND lk.lookup_key = 'pic_type'
//                AND tr.territory_id = f_get_territory(channel_id)
//                ORDER BY tgl DESC, partner_name ASC, pic_type ASC";
            
			//SQL query 
			// $inputQuery =  "select * from (SELECT CAST(cp.created_on AS DATE)  as tgl,
   //                          to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal
   //                          ,f_get_channel_name(channel_id) as partner_name
   //                          ,keyword, lk.lookup_desc as pic_type
   //                          , cp.image as picture
   //                          FROM t_trx_merchan_capture cp
   //                          , t_mtr_lookup lk
   //                          WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
   //                          and CAST(cp.created_on AS DATE) between date(now())-3 and date(now())
   //                          AND lk.lookup_key = 'pic_type'
   //                          AND f_get_cluster(user_id) = '$cluster') as aa where ''=''";
            $inputQuery = " SELECT created_on, to_char(created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
                            concat(channel_id, ' | ', f_get_channel_name(channel_id)) as partner_name,
                            keyword, concat(user_id, ' | ', f_get_user(user_id)) as sales_name,
                            image as image_url
                            FROM t_trx_merchan_capture
                            WHERE pic_type = $image_type
                            AND f_get_cluster(user_id) = $cluster_id";

			//execute datatables serversideprocess
			return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);    
    }
    
    public function getMerchants($image_type)
    {
        $sIndexColumn = "merchan_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $userlogin = $this->session->userdata('username'); //user credentials
        $userlogin = $this->session->userdata('username'); //user credentials
        $cluster_id = $this->mglobal->user_cluster($userlogin);
        $flag = '';
        $aColumns = array("merchan_id", "tanggal", "partner_name", "keyword", "sales_name", "image_url"); //field2 yg akan ditampilkan pada dataTables
        $inputQuery = "SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM v_ssp_merchandise_capture where cluster_id = $cluster_id and pic_type = $image_type";
        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, 'desc');
    }
    function getMerchants2($clusterid)
    {
            $sIndexColumn = "partner_name"; //index column untuk kecepatan akses data
			$sTable = "t_trx_merchan_capture"; //table pertama
			//$sTable2 = "t_mtr_territory"; //table kedua
			$defaultOrder = "2"; //default order berdasarkan field yg ada pada $aColumns
			$userlogin = $this->session->userdata('username'); //user credentials
			$aColumns = array("tanggal", "partner_name", "keyword", "pic_type", "picture"); //field2 yg akan ditampilkan pada dataTables
            $flag = "";
			
            //$sql = "SELECT CAST(cp.created_on AS DATE)  as tgl,
//                 to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal,
//                 f_get_channel_name(channel_id) as partner_name,
//                 keyword, lk.lookup_desc as pic_type, cp.image
//                 FROM t_trx_merchan_capture cp, t_mtr_lookup lk, t_mtr_territory tr
//                WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
//                AND lk.lookup_key = 'pic_type'
//                AND tr.territory_id = f_get_territory(channel_id)
//                ORDER BY tgl DESC, partner_name ASC, pic_type ASC";

            /**
             * SELECT CAST(cp.created_on AS DATE)  as tgl,
                            to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal
                            ,f_get_channel_name(channel_id) as partner_name
                            ,keyword, lk.lookup_desc as pic_type
                            , cp.image as picture
                            FROM t_trx_merchan_capture cp
                            , t_mtr_lookup lk
                            WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
                            AND lk.lookup_key = 'pic_type'
                            AND f_get_cluster(user_id) = f_get_cluster('$userlogin')            
             */             
            
			//SQL query 
			$inputQuery = "SELECT CAST(cp.created_on AS DATE)  as tgl,
                            to_char(cp.created_on, 'DD Mon YYYY - HH24:MI') as tanggal
                            ,f_get_channel_name(channel_id) as partner_name
                            ,keyword, lk.lookup_desc as pic_type
                            , cp.image as picture
                            FROM t_trx_merchan_capture cp
                            , t_mtr_lookup lk, t_mtr_territory tr
                            WHERE cp.pic_type = CAST(lk.lookup_value AS INTEGER)
                            AND lk.lookup_key = 'pic_type'
                            AND tr.parent_id = $clusterid
                            AND age(cp.created_on) <= (interval '2 days')";
			
			
			//execute datatables serversideprocess
			return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);    
    }        
    
} //end class